package com.anolesoft.amljmes.fortysix.service.mapper;

import cn.hutool.core.util.StrUtil;
import com.anolesoft.amljmes.fortysix.controller.MesBzzPtjhInfoController;
import com.anolesoft.amljmes.fortysix.dto.NewPtjhDto;
import com.anolesoft.amljmes.fortysix.dto.PtjhExport;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.jdbc.SQL;

import java.util.List;
/**
 * @author lx
 * @date 2022/11/21
 * 配套计划
 */
@Mapper
public interface MesBzzPtjhInfoServiceMapper extends BaseMapper<NewPtjhDto> {
    // 查询列表
    @SelectProvider(type=createSQL.class, method = "queryMesBzzPtjhInfoSQL")
    List<NewPtjhDto> queryMesBzzPtjhInfoSQL(MesBzzPtjhInfoController.DemoVO demoVO);
    public static class createSQL {
        public static String queryMesBzzPtjhInfoSQL(MesBzzPtjhInfoController.DemoVO demoVO){
            return new SQL(){
                {
                    StringBuilder sql = new StringBuilder();
                    sql.append(" a.objuid as key,a.*,0 as pageNo,10 as pageSize from mes_bzz_ptjh a where a.deletetag = 1 ");
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getFathernumber())){
                        String fathernumber = demoVO.getFathernumber();
                        sql.append(" and upper(a.fathernumber) = trim(upper(#{fathernumber, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getFathername())){
                        String fathername = demoVO.getFathername();
                        sql.append(" and upper(a.fathername) = trim(upper(#{fathername, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getDispatchnoofgeneral())){
                        String dispatchnoofgeneral = demoVO.getDispatchnoofgeneral();
                        sql.append(" and upper(a.dispatchnoofgeneral) = trim(upper(#{dispatchnoofgeneral, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getModel())){
                        String model = demoVO.getModel();
                        sql.append(" and upper(a.model) = trim(upper(#{model, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getBatch())){
                        String batch = demoVO.getBatch();
                        sql.append(" and upper(a.batch) = trim(upper(#{batch, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getSorties())){
                        String sorties = demoVO.getSorties();
                        sql.append(" and upper(a.sorties) = trim(upper(#{sorties, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getProportion())){
                        String proportion = demoVO.getProportion();
                        sql.append(" and upper(a.proportion) = trim(upper(#{proportion, jdbcType=VARCHAR}))");
                    }
                    if (demoVO.getGeneratematchingDocument() != null) {
                        sql.append(" and a.generatematching_document = trim(#{generatematchingDocument, jdbcType=NUMERIC})");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getFactorycode())){
                        String factorycode = demoVO.getFactorycode();
                        sql.append(" and upper(a.factorycode) = upper(#{factorycode, jdbcType=VARCHAR})");
                    }
                    SELECT(sql.toString());
                }
            }.toString();
        }
    }

//    查询未生成
    @SelectProvider(type=createSQL2.class, method = "queryMesBzzPtjhInfoSQL2")
    List<NewPtjhDto> queryMesBzzPtjhInfoSQL2(MesBzzPtjhInfoController.DemoVO demoVO);
    public static class createSQL2 {
        public static String queryMesBzzPtjhInfoSQL2(MesBzzPtjhInfoController.DemoVO demoVO){
            return new SQL(){
                {
                    StringBuilder sql = new StringBuilder();
                    sql.append(" a.objuid as key,a.*,0 as pageNo,10 as pageSize from mes_bzz_ptjh a where a.deletetag = 1 and a.generatematching_document = 0");
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getFathernumber())){
                        String fathernumber = demoVO.getFathernumber();
                        sql.append(" and upper(a.fathernumber) = trim(upper(#{fathernumber, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getFathername())){
                        String fathername = demoVO.getFathername();
                        sql.append(" and upper(a.fathername) = trim(upper(#{fathername, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getDispatchnoofgeneral())){
                        String dispatchnoofgeneral = demoVO.getDispatchnoofgeneral();
                        sql.append(" and upper(a.dispatchnoofgeneral) = trim(upper(#{dispatchnoofgeneral, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getModel())){
                        String model = demoVO.getModel();
                        sql.append(" and upper(a.model) = trim(upper(#{model, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getBatch())){
                        String batch = demoVO.getBatch();
                        sql.append(" and upper(a.batch) = trim(upper(#{batch, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getSorties())){
                        String sorties = demoVO.getSorties();
                        sql.append(" and upper(a.sorties) = trim(upper(#{sorties, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getProportion())){
                        String proportion = demoVO.getProportion();
                        sql.append(" and upper(a.proportion) = trim(upper(#{proportion, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getFactorycode())){
                        String factorycode = demoVO.getFactorycode();
                        sql.append(" and upper(a.factorycode) = upper(#{factorycode, jdbcType=VARCHAR})");
                    }
                    SELECT(sql.toString());
                }
            }.toString();
        }
    }

//    查询配套单主键
    @Select("select objuid\n" +
            "  from mes_bzz_ptd a\n" +
            " where upper(a.fathernumber) = trim(upper(#{fathernumber, jdbcType = VARCHAR}))\n" +
            "   and upper(a.fathername) = trim(upper(#{fathername, jdbcType = VARCHAR}))\n" +
            "   and upper(a.dispatch) = trim(upper(#{dispatchnoofgeneral, jdbcType = VARCHAR}))\n" +
            "   and upper(a.model) = trim(upper(#{model, jdbcType = VARCHAR}))\n" +
            "   and upper(a.batch) = trim(upper(#{batch, jdbcType = VARCHAR}))\n" +
            "   and upper(a.sorties) = trim(upper(#{sorties, jdbcType = VARCHAR}))\n" +
            "   and upper(a.proportion) = trim(upper(#{proportion, jdbcType = VARCHAR}))\n" +
            "   and upper(a.factory_code) = upper(#{factorycode, jdbcType = VARCHAR})")
    String queryMesBzzPtd(MesBzzPtjhInfoController.DemoVO demoVO);

    // 修改
    @Update("    update mes_bzz_ptjh " +
            "      set wonumber = #{wonumber,jdbcType = VARCHAR},\n" +
            "  sonumber = #{sonumber,jdbcType = VARCHAR},\n" +
            "  monumber = #{monumber,jdbcType = VARCHAR},\n" +
            "  fathernumber = #{fathernumber,jdbcType = VARCHAR},\n" +
            "  fathername = #{fathername,jdbcType = VARCHAR},\n" +
            "  model = #{model,jdbcType = VARCHAR},\n" +
            "  batch = #{batch,jdbcType = VARCHAR},\n" +
            "  sorties = #{sorties,jdbcType = VARCHAR},\n" +
            "  dispatchnoofgeneral = #{dispatchnoofgeneral,jdbcType = VARCHAR},\n" +
            "  serialnumber = #{serialnumber,jdbcType = VARCHAR},\n" +
            "  name = #{name,jdbcType = VARCHAR},\n" +
            "  itemno = #{itemno,jdbcType = VARCHAR},\n" +
            "  divisionroute = #{divisionroute,jdbcType = VARCHAR},\n" +
            "  scheduledstarttime = #{scheduledstarttime, jdbcType=DATE},\n" +
            "  scheduledendtime = #{scheduledendtime, jdbcType=DATE},\n" +
            "  drawingno = #{drawingno,jdbcType = VARCHAR},\n" +
            "  deliveryworkshop = #{deliveryworkshop,jdbcType = VARCHAR},\n" +
            "  receivingworkshop = #{receivingworkshop,jdbcType = VARCHAR},\n" +
            "  deliverysequence = #{deliverysequence,jdbcType = VARCHAR},\n" +
            "  materialtype = #{materialtype,jdbcType = VARCHAR},\n" +
            "  demandquantity = #{demandquantity,jdbcType = VARCHAR}, " +
            "  proportion = #{proportion,jdbcType = VARCHAR}\n" +
            "    where objuid = #{objuid, jdbcType=VARCHAR}"
    )
    int updatePtjh(NewPtjhDto inFoDtoParam);

    //批量删除
    @Update("update mes_bzz_ptjh set deletetag = 0,deletetime = sysdate where objuid = #{objuid, jdbcType=VARCHAR}" )
    int delete(String objuid);

    //导出查询
    @SelectProvider(type=createSQL1.class, method = "exportPtjhSQL")
    List<PtjhExport> exportPtjh(MesBzzPtjhInfoController.DemoVO demoVO);
    public static class createSQL1 {
        public static String exportPtjhSQL(MesBzzPtjhInfoController.DemoVO demoVO){
            return new SQL(){
                {
                    StringBuilder sql = new StringBuilder();
                    sql.append(" a.objuid as key,\n" +
                            "       a.objuid,\n" +
                            "       a.wonumber,\n" +
                            "       a.sonumber,\n" +
                            "       a.fathernumber,\n" +
                            "       a.fathername,\n" +
                            "       a.model,\n" +
                            "       a.batch,\n" +
                            "       a.sorties,\n" +
                            "       a.dispatchnoofgeneral,\n" +
                            "       a.serialnumber,\n" +
                            "       a.name,\n" +
                            "       a.itemno,\n" +
                            "       a.divisionroute,\n" +
                            "       to_char(a.scheduledstarttime, 'yyyy-mm-dd') scheduledstarttime,\n" +
                            "       to_char(a.scheduledendtime, 'yyyy-mm-dd') scheduledendtime,\n" +
                            "       a.drawingno,\n" +
                            "       a.deliveryworkshop,\n" +
                            "       a.receivingworkshop,\n" +
                            "       a.deliverysequence,\n" +
                            "       case\n" +
                            "         when a.generatematching_document = 0 then\n" +
                            "          '未生成'\n" +
                            "         when a.generatematching_document = 1 then\n" +
                            "          '已生成'\n" +
                            "         else\n" +
                            "          to_char(a.generatematching_document)\n" +
                            "       end as generatematching_document,\n" +
                            "       a.factorycode,\n" +
                            "       a.materialtype,\n" +
                            "       a.demandquantity,\n" +
                            "       a.proportion,\n" +
                            "       a.affiliation,\n" +
                            "       a.monumber,\n" +
                            "       0 as pageNo,\n" +
                            "       10 as pageSize\n" +
                            "  from mes_bzz_ptjh a\n" +
                            " where a.deletetag = 1\n ");
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getFathernumber())){
                        String fathernumber = demoVO.getFathernumber();
                        sql.append(" and upper(a.fathernumber) = trim(upper(#{fathernumber, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getFathername())){
                        String fathername = demoVO.getFathername();
                        sql.append(" and upper(a.fathername) = trim(upper(#{fathername, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getDispatchnoofgeneral())){
                        String dispatchnoofgeneral = demoVO.getDispatchnoofgeneral();
                        sql.append(" and upper(a.dispatchnoofgeneral) = trim(upper(#{dispatchnoofgeneral, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getModel())){
                        String model = demoVO.getModel();
                        sql.append(" and upper(a.model) = trim(upper(#{model, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getBatch())){
                        String batch = demoVO.getBatch();
                        sql.append(" and upper(a.batch) = trim(upper(#{batch, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getSorties())){
                        String sorties = demoVO.getSorties();
                        sql.append(" and upper(a.sorties) = trim(upper(#{sorties, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getProportion())){
                        String proportion = demoVO.getProportion();
                        sql.append(" and upper(a.proportion) = trim(upper(#{proportion, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getFactorycode())){
                        String factorycode = demoVO.getFactorycode();
                        sql.append(" and upper(a.factorycode) = upper(#{factorycode, jdbcType=VARCHAR})");
                    }
                    SELECT(sql.toString());
                }
            }.toString();
        }
    }
}
